package imse.team1.inventory.dal;

import org.hsqldb.jdbc.JDBCDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.concurrent.Semaphore;

/**
 * @author Eugen, 6/18/12 7:42 PM
 */
public class CatalogQueryService {
    private static DataSource ds;
    private final static String CATALOGDB_LOCATION = "localhost";
    private final static Connection[] connectionPool = new Connection[5];
    private final static Semaphore[] connectionLock = new Semaphore[connectionPool.length];
    private static Random random = new Random();

    static {
        JDBCDataSource dataSource = new JDBCDataSource();
        dataSource.setUrl("jdbc:hsqldb:hsql://" + CATALOGDB_LOCATION + "/wineinventory");
        dataSource.setUser("TEAM1");
        dataSource.setPassword("bpelproject");
        ds = dataSource;
        for (int i = 0; i < connectionPool.length; i++) {
            try {
                connectionPool[i] = ds.getConnection();
            } catch (SQLException e) {
                System.out.println("Global Inventory: Couldn't get connection #" + i);
                e.printStackTrace();
            }
        }
        for (int i = 0; i < connectionPool.length; i++) {
            connectionLock[i] = new Semaphore(1);
        }
        Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
            public void run() {
                try {
                    for (int i = 0; i < connectionPool.length; i++) {
                        connectionPool[i].close();
                    }
                } catch (SQLException ex) {
                    System.out.println(ex);
                }
                for (int i = 0; i < connectionLock.length; i++)
                {
                    connectionLock[i].release();
                }
            }
        }));
    }

    private static class SQLRepo {
        private static final String verifyNameInCatalog = "SELECT unitprice FROM bpelproject.\"WineCatalog\" WHERE name = ?";
    }

    private static int getConnection() throws Exception {
        int i = random.nextInt(connectionPool.length);
        try {
            connectionLock[i].acquire();
            if (connectionPool[i].isClosed()) {
                synchronized (CatalogQueryService.class) {
                    if (connectionPool[i].isClosed()) {
                        System.out.println("Global Inventory: Connection " + i + " was closed!");
                        connectionPool[i] = ds.getConnection();
                        return i;
                    }
                }
            }
            return i;
        } catch (Exception ex) {
            connectionLock[i].release();
            throw ex;
        }
    }

    // first element in list indicates whether all items where found or not: if null - all elements found, otherwise the missing names are returned
    public List<String> checkValidNames(List<String> names) throws Exception{
        List<String> result = new ArrayList<String>(2);
        result.add(null);
        int index = getConnection();
        Connection conn = connectionPool[index];
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(SQLRepo.verifyNameInCatalog);
            for (int i = 0; i < names.size(); ++i) {
                stmt.setString(1, names.get(i));
                ResultSet rs = stmt.executeQuery();
                if (!rs.next()){// not found
                    result.clear();
                    result.add("");
                    result.add(names.get(i));
                    break;
                }
                else {
                    result.add(rs.getDouble("unitPrice")+"");
                }
            }
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            connectionLock[index].release();
        }
        return result;
    }
}
